﻿using System;
using System.Collections.Generic;
using System.Text;
using Net4.Common.Services;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using Net4.Common.Entities;
using Net4.Common.Data;

namespace Net4.Services.Access
{
    public class AccessMessageService : IMessageService
    {
        private static AccessMessageService _Instance = new AccessMessageService();

        public static AccessMessageService Instance
        {
            get { return AccessMessageService._Instance; }
        }

        #region IMessageService Members

        
        private static readonly string INSERT_MESSAGE = 
@"INSERT INTO [Messages]([UserName], [Email], [Subject], [Body], [UserIP], [CreateDate]) 
    VALUES(@UserName, @Email, @Subject, @Body, @UserIP, @CreateDate)";
        /// <summary>
        /// Inserts the specified message.
        /// </summary>
        /// <param name="message">The message.</param>
        public void Insert(Message message)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(INSERT_MESSAGE);
            db.AddInParameter(cmd, "@UserName", DbType.String, message.UserName);
            db.AddInParameter(cmd, "@Email", DbType.String, message.Email);
            db.AddInParameter(cmd, "@Subject", DbType.String, message.Subject);
            db.AddInParameter(cmd, "@Body", DbType.String, message.Body);
            db.AddInParameter(cmd, "@UserIP", DbType.String, message.UserIP);
            /*有些语句确实和Access数据库不太兼容，如
            db.AddInParameter(dbCommand, "@DateUpdate", DbType.DateTime, DateTime.Now)在Access数据库环境下就会提示“标准表达式中数据类型不匹配。”，只能用
            db.AddInParameter(dbCommand, "@DateUpdate", DbType.DateTime, DateTime.Now.ToString ())或db.AddInParameter(dbCommand, "@DateUpdate", DbType.String,  DateTime.Now)或
            db.AddInParameter(dbCommand, "@DateUpdate", DbType.String, DateTime.Now.ToString());
             */
            db.AddInParameter(cmd, "@CreateDate", DbType.DateTime, message.CreateDate.ToString());

            //要使Select @@IDENTITY返回正确的值，必须保证在同一事务内完成
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                try
                {
                    // insert the first message.
                    db.ExecuteNonQuery(cmd, transaction);

                    cmd.Parameters.Clear();
                    cmd.CommandText = Utilis.GET_LAST_INSERT_IDENTITY;
                    message.Id = (int)db.ExecuteScalar(cmd, transaction);

                    // Commit the transaction.
                    transaction.Commit();
                }
                catch(Exception e)
                {
                    // Roll back the transaction. 
                    transaction.Rollback();
                    throw e;
                }
                finally
                {
                    connection.Close();
                }
            }
            
        }

        private static readonly string UPDATE_MESSAGE =
@"UPDATE [Messages] SET [UserName]=@UserName, [Email]=@Email, [Subject]=@Subject, 
    [Body]=@Body, [Reply]=@Reply, [ReplyDate]=@ReplyDate WHERE [Id]=@Id";
        public void Update(Message message)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(UPDATE_MESSAGE);
            db.AddInParameter(cmd, "@UserName", DbType.String, message.UserName);
            db.AddInParameter(cmd, "@Email", DbType.String, message.Email);
            db.AddInParameter(cmd, "@Subject", DbType.String, message.Subject);
            db.AddInParameter(cmd, "@Body", DbType.String, message.Body);
            db.AddInParameter(cmd, "@Reply", DbType.String, message.Reply);
            db.AddInParameter(cmd, "@ReplyDate", DbType.DateTime, message.ReplyDate.ToString());
            db.AddInParameter(cmd, "@Id", DbType.Int32, message.Id);

            db.ExecuteNonQuery(cmd);
        }

        private static readonly string DELETE_MESSAGE =
@"DELETE FROM [Messages] WHERE [Id]=@Id";

        public void Delete(int messageId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(DELETE_MESSAGE);
            db.AddInParameter(cmd, "@Id", DbType.Int32, messageId);

            db.ExecuteNonQuery(cmd);
        }

        private static readonly string SELECT_MESSAGE =
@"SELECT * FROM [Messages] WHERE [Id]=@Id";
        public Message Select(int messageId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(SELECT_MESSAGE);
            db.AddInParameter(cmd, "@Id", DbType.Int32, messageId);
            using (SafeDataReader reader = new SafeDataReader(db.ExecuteReader(cmd)))
            {
                if (reader.Read())
                {
                    Message msg = new Message(reader.GetInt32("Id"),
                        reader.GetString("UserName"),
                        reader.GetString("Email"),
                        reader.GetString("Subject"),
                        reader.GetString("Body"),
                        reader.GetString("UserIP"),
                        reader.GetDateTime("CreateDate"),
                        reader.GetString("Reply"),
                        reader.GetDateTime("ReplyDate"));
                    return msg;
                }
                return null;
            }
        }

        private static readonly string SELECT_MESSAGELIST_PAGE = 
@"SELECT TOP {0} * FROM [Messages] 
WHERE ([Id] < (SELECT MIN([Id]) FROM (SELECT TOP {1} [Id] FROM [Messages] ORDER BY [Id] DESC) AS T))
ORDER BY [Id] DESC
";
        private static readonly string SELECT_MESSAGELIST = 
@"SELECT TOP {0} * FROM [Messages] ORDER BY [Id] DESC";

        public List<Message> SelectMessages(int pageIndex, int pageMaxCount)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(string.Format(SELECT_MESSAGELIST, pageMaxCount));
            if (pageIndex > 1)
            {
                cmd.CommandText = string.Format(SELECT_MESSAGELIST_PAGE, pageMaxCount, (pageIndex-1) * pageMaxCount);
            }
            using (SafeDataReader reader = new SafeDataReader(db.ExecuteReader(cmd)))
            {
                List<Message> list = new List<Message>();
                while (reader.Read())
                {
                    list.Add(new Message(reader.GetInt32("Id"),
                        reader.GetString("UserName"),
                        reader.GetString("Email"),
                        reader.GetString("Subject"),
                        reader.GetString("Body"),
                        reader.GetString("UserIP"),
                        reader.GetDateTime("CreateDate"), 
                        reader.GetString("Reply"),
                        reader.GetDateTime("ReplyDate")));
                }
                return list;
            }
        }

        public int GetMessagesTotalCount()
        { 
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT COUNT([Id]) FROM [Messages]");
            return (int)db.ExecuteScalar(cmd);
        }

        #endregion
    }
}
